﻿CREATE PROCEDURE [dbo].[yaf_forum_listpath](
                @ForumID INT)
AS
    BEGIN
        -- supports up to 4 levels of nested forums
        SELECT a.ForumID,
               a.Name
        FROM     (SELECT a.ForumID,
                         Indent = 0
                  FROM   yaf_Forum a
                  WHERE  a.ForumID = @ForumID
                  UNION 
                  SELECT b.ForumID,
                         Indent = 1
                  FROM   yaf_Forum a
                         JOIN yaf_Forum b
                           ON b.ForumID = a.ParentID
                  WHERE  a.ForumID = @ForumID
                  UNION 
                  SELECT c.ForumID,
                         Indent = 2
                  FROM   yaf_Forum a
                         JOIN yaf_Forum b
                           ON b.ForumID = a.ParentID
                         JOIN yaf_Forum c
                           ON c.ForumID = b.ParentID
                  WHERE  a.ForumID = @ForumID
                  UNION 
                  SELECT d.ForumID,
                         Indent = 3
                  FROM   yaf_Forum a
                         JOIN yaf_Forum b
                           ON b.ForumID = a.ParentID
                         JOIN yaf_Forum c
                           ON c.ForumID = b.ParentID
                         JOIN yaf_Forum d
                           ON d.ForumID = c.ParentID
                  WHERE  a.ForumID = @ForumID) AS x
                 JOIN yaf_Forum a
                   ON a.ForumID = x.ForumID
        ORDER BY x.Indent DESC
    END


